- frmBusFeePayment_StudentRecord.vb
- project /
1 Imports System.Data.SqlClient
2 Imports System.IO
3 Imports Excel = Microsoft.Office.Interop.Excel
4 Public Class frmBusFeePayment_StudentRecord
5 Public Sub GetData()
6 Try
7 con = New SqlConnection(cs)
8 con.Open()
9 cmd = New SqlCommand("Select RTRIM(BusFeePayment_Student.Id) as [ID], RTRIM(BFP_ID) as [BFP ID], RTRIM(PaymentID) as [Payment ID],RTRIM(BusHolderID) as [Bus Holder ID], RTRIM(Student.AdmissionNo) as [Admission No.],RTRIM(StudentName) as [StudentName],RTRIM(EnrollmentNo) as [Enrollment No.],RTRIM(SchoolName) as [School Name],RTRIM(BusCardHolder_Student.Location) as [Location],RTRIM(BusFeePayment_Student.Class) as [Class],RTRIM(BusFeePayment_Student.Section) as [Section], RTRIM(BusFeePayment_Student.Session) as [Session],RTRIM(installment) as [installment], RTRIM(TotalFee) as [Total Fee], RTRIM(DiscountPer) as [Discount %], RTRIM(DiscountAmt) as [Discount], RTRIM(PreviousDue) as [Previous Due], RTRIM(Fine) as [Fine], RTRIM(GrandTotal) as [Grand Total], RTRIM(TotalPaid) as [Total Paid], RTRIM(ModeOfPayment) as [Payment Mode], RTRIM(PaymentModeDetails) as [Payement Mode Info], Convert(Datetime,PaymentDate,131) as [Payement Date], RTRIM(PaymentDue) as [Payement Due],RTRIM(BusFeePayment_Student.ClassType) as [Class Type], RTRIM(BusFeePayment_Student.SchoolType) as [School Type] from Student,SchoolInfo,BusFeePayment_Student,BusCardHolder_Student where SchoolInfo.S_ID=Student.SchoolID and BusCardHolder_Student.BCH_ID=BusFeePayment_Student.BusHolderID and BusCardHolder_Student.AdmissionNo=Student.AdmissionNo order by StudentName", con)
10 adp = New SqlDataAdapter(cmd)
11 ds = New DataSet()
12 adp.Fill(ds, "Student")
13 dgw.DataSource = ds.Tables("Student").DefaultView
14 con.Close()
15 Catch ex As Exception
16 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
17 End Try
18 End Sub
19
20 Private Sub Button4_Click(sender As System.Object, e As System.EventArgs) Handles Button4.Click
21 Me.Close()
22 End Sub
23
24 Private Sub txtStudentName_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtStudentName.TextChanged
25 Try
26 con = New SqlConnection(cs)
27 con.Open()
28 cmd = New SqlCommand("Select RTRIM(BusFeePayment_Student.Id) as [ID], RTRIM(BFP_ID) as [BFP ID], RTRIM(PaymentID) as [Payment ID],RTRIM(BusHolderID) as [Bus Holder ID], RTRIM(Student.AdmissionNo) as [Admission No.],RTRIM(StudentName) as [StudentName],RTRIM(EnrollmentNo) as [Enrollment No.],RTRIM(SchoolName) as [School Name],RTRIM(BusCardHolder_Student.Location) as [Location],RTRIM(BusFeePayment_Student.Class) as [Class],RTRIM(BusFeePayment_Student.Section) as [Section], RTRIM(BusFeePayment_Student.Session) as [Session],RTRIM(installment) as [installment], RTRIM(TotalFee) as [Total Fee], RTRIM(DiscountPer) as [Discount %], RTRIM(DiscountAmt) as [Discount], RTRIM(PreviousDue) as [Previous Due], RTRIM(Fine) as [Fine], RTRIM(GrandTotal) as [Grand Total], RTRIM(TotalPaid) as [Total Paid], RTRIM(ModeOfPayment) as [Payment Mode], RTRIM(PaymentModeDetails) as [Payement Mode Info], Convert(Datetime,PaymentDate,131) as [Payement Date], RTRIM(PaymentDue) as [Payement Due],RTRIM(BusFeePayment_Student.ClassType) as [Class Type], RTRIM(BusFeePayment_Student.SchoolType) as [School Type] from Student,SchoolInfo,BusFeePayment_Student,BusCardHolder_Student where SchoolInfo.S_ID=Student.SchoolID and BusCardHolder_Student.BCH_ID=BusFeePayment_Student.BusHolderID and BusCardHolder_Student.AdmissionNo=Student.AdmissionNo and StudentName like '" & txtStudentName.Text & "%' order by StudentName", con)
29 adp = New SqlDataAdapter(cmd)
30 ds = New DataSet()
31 adp.Fill(ds, "Student")
32 dgw.DataSource = ds.Tables("Student").DefaultView
33 con.Close()
34 Catch ex As Exception
35 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
36 End Try
37 End Sub
38
39 Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
40 Try
41 con = New SqlConnection(cs)
42 con.Open()
43 cmd = New SqlCommand("Select RTRIM(BusFeePayment_Student.Id) as [ID], RTRIM(BFP_ID) as [BFP ID], RTRIM(PaymentID) as [Payment ID],RTRIM(BusHolderID) as [Bus Holder ID], RTRIM(Student.AdmissionNo) as [Admission No.],RTRIM(StudentName) as [StudentName],RTRIM(EnrollmentNo) as [Enrollment No.],RTRIM(SchoolName) as [School Name],RTRIM(BusCardHolder_Student.Location) as [Location],RTRIM(BusFeePayment_Student.Class) as [Class],RTRIM(BusFeePayment_Student.Section) as [Section], RTRIM(BusFeePayment_Student.Session) as [Session],RTRIM(installment) as [installment], RTRIM(TotalFee) as [Total Fee], RTRIM(DiscountPer) as [Discount %], RTRIM(DiscountAmt) as [Discount], RTRIM(PreviousDue) as [Previous Due], RTRIM(Fine) as [Fine], RTRIM(GrandTotal) as [Grand Total], RTRIM(TotalPaid) as [Total Paid], RTRIM(ModeOfPayment) as [Payment Mode], RTRIM(PaymentModeDetails) as [Payement Mode Info], Convert(Datetime,PaymentDate,131) as [Payement Date], RTRIM(PaymentDue) as [Payement Due],RTRIM(BusFeePayment_Student.ClassType) as [Class Type], RTRIM(BusFeePayment_Student.SchoolType) as [School Type] from Student,SchoolInfo,BusFeePayment_Student,BusCardHolder_Student where SchoolInfo.S_ID=Student.SchoolID and BusCardHolder_Student.BCH_ID=BusFeePayment_Student.BusHolderID and BusCardHolder_Student.AdmissionNo=Student.AdmissionNo and BusFeePayment_Student.Session=@d1 and BusFeePayment_Student.Class=@d2 order by StudentName", con)
44 cmd.Parameters.AddWithValue("@d1", cmbSession.Text)
45 cmd.Parameters.AddWithValue("@d2", cmbClass.Text)
46 adp = New SqlDataAdapter(cmd)
47 ds = New DataSet()
48 adp.Fill(ds, "Student")
49 dgw.DataSource = ds.Tables("Student").DefaultView
50 con.Close()
51 Catch ex As Exception
52 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
53 End Try
54 End Sub
55
56 Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
57 Try
58 con = New SqlConnection(cs)
59 con.Open()
60 cmd = New SqlCommand("Select RTRIM(BusFeePayment_Student.Id) as [ID], RTRIM(BFP_ID) as [BFP ID], RTRIM(PaymentID) as [Payment ID],RTRIM(BusHolderID) as [Bus Holder ID], RTRIM(Student.AdmissionNo) as [Admission No.],RTRIM(StudentName) as [StudentName],RTRIM(EnrollmentNo) as [Enrollment No.],RTRIM(SchoolName) as [School Name],RTRIM(BusCardHolder_Student.Location) as [Location],RTRIM(BusFeePayment_Student.Class) as [Class],RTRIM(BusFeePayment_Student.Section) as [Section], RTRIM(BusFeePayment_Student.Session) as [Session],RTRIM(installment) as [installment], RTRIM(TotalFee) as [Total Fee], RTRIM(DiscountPer) as [Discount %], RTRIM(DiscountAmt) as [Discount], RTRIM(PreviousDue) as [Previous Due], RTRIM(Fine) as [Fine], RTRIM(GrandTotal) as [Grand Total], RTRIM(TotalPaid) as [Total Paid], RTRIM(ModeOfPayment) as [Payment Mode], RTRIM(PaymentModeDetails) as [Payement Mode Info], Convert(Datetime,PaymentDate,131) as [Payement Date], RTRIM(PaymentDue) as [Payement Due],RTRIM(BusFeePayment_Student.ClassType) as [Class Type], RTRIM(BusFeePayment_Student.SchoolType) as [School Type] from Student,SchoolInfo,BusFeePayment_Student,BusCardHolder_Student where SchoolInfo.S_ID=Student.SchoolID and BusCardHolder_Student.BCH_ID=BusFeePayment_Student.BusHolderID and BusCardHolder_Student.AdmissionNo=Student.AdmissionNo and PaymentDate between @d1 and @d2 order by StudentName", con)
61 cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "Date").Value = dtpDateFrom.Value.Date
62 cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "Date").Value = dtpDateTo.Value
63 adp = New SqlDataAdapter(cmd)
64 ds = New DataSet()
65 adp.Fill(ds, "Student")
66 dgw.DataSource = ds.Tables("Student").DefaultView
67 con.Close()
68 Catch ex As Exception
69 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
70 End Try
71 End Sub
72
73 Sub fillSession()
74 Try
75 con = New SqlConnection(cs)
76 con.Open()
77 adp = New SqlDataAdapter()
78 adp.SelectCommand = New SqlCommand("SELECT distinct (Session) FROM BusFeePayment_Student", con)
79 ds = New DataSet("ds")
80 adp.Fill(ds)
81 dtable = ds.Tables(0)
82 cmbSession.Items.Clear()
83 For Each drow As DataRow In dtable.Rows
84 cmbSession.Items.Add(drow(0).ToString())
85 Next
86 Catch ex As Exception
87 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
88 End Try
89 End Sub
90
91 Private Sub cmbSession_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cmbSession.SelectedIndexChanged
92 Try
93 cmbClass.Enabled = True
94 con = New SqlConnection(cs)
95 con.Open()
96 Dim ct As String = "SELECT distinct RTRIM(Class) FROM BusFeePayment_Student where BusFeePayment_Student.Session=@d1"
97 cmd = New SqlCommand(ct)
98 cmd.Connection = con
99 cmd.Parameters.AddWithValue("@d1", cmbSession.Text)
100 rdr = cmd.ExecuteReader()
101 cmbClass.Items.Clear()
102 While rdr.Read
103 cmbClass.Items.Add(rdr(0))
104 End While
105 con.Close()
106 Catch ex As Exception
107 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
108 End Try
109
110 End Sub
111
112
113 Private Sub txtAdmissionNo_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtAdmissionNo.TextChanged
114 Try
115 con = New SqlConnection(cs)
116 con.Open()
117 cmd = New SqlCommand("Select RTRIM(BusFeePayment_Student.Id) as [ID], RTRIM(BFP_ID) as [BFP ID], RTRIM(PaymentID) as [Payment ID],RTRIM(BusHolderID) as [Bus Holder ID], RTRIM(Student.AdmissionNo) as [Admission No.],RTRIM(StudentName) as [StudentName],RTRIM(EnrollmentNo) as [Enrollment No.],RTRIM(SchoolName) as [School Name],RTRIM(BusCardHolder_Student.Location) as [Location],RTRIM(BusFeePayment_Student.Class) as [Class],RTRIM(BusFeePayment_Student.Section) as [Section], RTRIM(BusFeePayment_Student.Session) as [Session],RTRIM(installment) as [installment], RTRIM(TotalFee) as [Total Fee], RTRIM(DiscountPer) as [Discount %], RTRIM(DiscountAmt) as [Discount], RTRIM(PreviousDue) as [Previous Due], RTRIM(Fine) as [Fine], RTRIM(GrandTotal) as [Grand Total], RTRIM(TotalPaid) as [Total Paid], RTRIM(ModeOfPayment) as [Payment Mode], RTRIM(PaymentModeDetails) as [Payement Mode Info], Convert(Datetime,PaymentDate,131) as [Payement Date], RTRIM(PaymentDue) as [Payement Due],RTRIM(BusFeePayment_Student.ClassType) as [Class Type], RTRIM(BusFeePayment_Student.SchoolType) as [School Type] from Student,SchoolInfo,BusFeePayment_Student,BusCardHolder_Student where SchoolInfo.S_ID=Student.SchoolID and BusCardHolder_Student.BCH_ID=BusFeePayment_Student.BusHolderID and BusCardHolder_Student.AdmissionNo=Student.AdmissionNo and Student.AdmissionNo like '" & txtAdmissionNo.Text & "%' order by StudentName", con)
118 adp = New SqlDataAdapter(cmd)
119 ds = New DataSet()
120 adp.Fill(ds, "Student")
121 dgw.DataSource = ds.Tables("Student").DefaultView
122 con.Close()
123 Catch ex As Exception
124 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
125 End Try
126 End Sub
127 Sub Reset()
128 txtAdmissionNo.Text = ""
129 txtStudentName.Text = ""
130 cmbClass.SelectedIndex = -1
131 cmbSession.SelectedIndex = -1
132 cmbClass.Enabled = False
133 dtpDateFrom.Text = Today
134 dtpDateTo.Text = Now
135 GetData()
136 End Sub
137 Private Sub btnReset_Click(sender As System.Object, e As System.EventArgs) Handles btnReset.Click
138 Reset()
139 End Sub
140
141 Private Sub frmStudentRecord_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
142 fillSession()
143 GetData()
144 End Sub
145
146 Private Sub dgw_MouseClick(sender As Object, e As System.Windows.Forms.MouseEventArgs) Handles dgw.MouseClick
147 Try
148 Dim dr As DataGridViewRow = dgw.SelectedRows(0)
149 If lblSet.Text = "Bus Fee Payment" Then
150 Me.Hide()
151 frmBusFeePayment_Student.Show()
152 frmBusFeePayment_Student.txtID.Text = dr.Cells(0).Value.ToString()
153 frmBusFeePayment_Student.cmbInstallment.DropDownStyle = ComboBoxStyle.DropDown
154 frmBusFeePayment_Student.txtBFPId.Text = dr.Cells(1).Value.ToString()
155 frmBusFeePayment_Student.txtFeePaymentID.Text = dr.Cells(2).Value.ToString()
156 frmBusFeePayment_Student.txtBusHolderID.Text = dr.Cells(3).Value.ToString()
157 frmBusFeePayment_Student.txtAdmissionNo.Text = dr.Cells(4).Value.ToString()
158 frmBusFeePayment_Student.txtStudentName.Text = dr.Cells(5).Value.ToString()
159 frmBusFeePayment_Student.txtEnrollmentNo.Text = dr.Cells(6).Value.ToString()
160 frmBusFeePayment_Student.txtLocation.Text = dr.Cells(7).Value.ToString() '
161 frmBusFeePayment_Student.txtSchoolName.Text = dr.Cells(8).Value.ToString()
162 frmBusFeePayment_Student.txtClass.Text = dr.Cells(9).Value.ToString()
163 frmBusFeePayment_Student.txtSection.Text = dr.Cells(10).Value.ToString()
164 frmBusFeePayment_Student.txtSession.Text = dr.Cells(11).Value.ToString()
165 frmBusFeePayment_Student.cmbInstallment.Text = dr.Cells(12).Value.ToString()
166 frmBusFeePayment_Student.txtBusFee.Text = dr.Cells(13).Value.ToString()
167 frmBusFeePayment_Student.txtDiscountPer.Text = dr.Cells(14).Value.ToString()
168 frmBusFeePayment_Student.txtDiscount.Text = dr.Cells(15).Value.ToString()
169 frmBusFeePayment_Student.txtPreviousDue.Text = dr.Cells(16).Value.ToString()
170 frmBusFeePayment_Student.txtFine.Text = dr.Cells(17).Value.ToString()
171 frmBusFeePayment_Student.txtGrandTotal.Text = dr.Cells(18).Value.ToString()
172 frmBusFeePayment_Student.txtTotalPaid.Text = dr.Cells(19).Value.ToString()
173 frmBusFeePayment_Student.cmbPaymentMode.Text = dr.Cells(20).Value.ToString()
174 frmBusFeePayment_Student.txtPaymentModeDetails.Text = dr.Cells(21).Value.ToString()
175 frmBusFeePayment_Student.dtpPaymentDate.Text = dr.Cells(22).Value.ToString()
176 frmBusFeePayment_Student.txtBalance.Text = dr.Cells(23).Value.ToString()
177 frmBusFeePayment_Student.txtClassType.Text = dr.Cells(24).Value.ToString()
178 frmBusFeePayment_Student.txtSchoolType.Text = dr.Cells(25).Value.ToString()
179 frmBusFeePayment_Student.btnDelete.Enabled = True
180 frmBusFeePayment_Student.btnUpdate.Enabled = True
181 frmBusFeePayment_Student.btnSave.Enabled = False
182 frmBusFeePayment_Student.Button2.Enabled = False
183 frmBusFeePayment_Student.dtpPaymentDate.Enabled = False
184 frmBusFeePayment_Student.btnPrint.Enabled = True
185 frmBusFeePayment_Student.cmbInstallment.Enabled = False
186
187 lblSet.Text = ""
188 End If
189
190 Catch ex As Exception
191 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
192 End Try
193 End Sub
194
195 Private Sub dgw_RowPostPaint(sender As Object, e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
196 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
197 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
198 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
199 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
200 End If
201 Dim b As Brush = SystemBrushes.ControlText
202 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
203
204 End Sub
205
206 Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
207 Dim rowsTotal, colsTotal As Short
208 Dim I, j, iC As Short
209 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
210 Dim xlApp As New Excel.Application
211 Try
212 Dim excelBook As Excel.Workbook = xlApp.Workbooks.Add
213 Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
214 xlApp.Visible = True
215
216 rowsTotal = dgw.RowCount
217 colsTotal = dgw.Columns.Count - 1
218 With excelWorksheet
219 .Cells.Select()
220 .Cells.Delete()
221 For iC = 0 To colsTotal
222 .Cells(1, iC + 1).Value = dgw.Columns(iC).HeaderText
223 Next
224 For I = 0 To rowsTotal - 1
225 For j = 0 To colsTotal
226 .Cells(I + 2, j + 1).value = dgw.Rows(I).Cells(j).Value
227 Next j
228 Next I
229 .Rows("1:1").Font.FontStyle = "Bold"
230 .Rows("1:1").Font.Size = 12
231
232 .Cells.Columns.AutoFit()
233 .Cells.Select()
234 .Cells.EntireColumn.AutoFit()
235 .Cells(1, 1).Select()
236 End With
237 Catch ex As Exception
238 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
239 Finally
240 'RELEASE ALLOACTED RESOURCES
241 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
242 xlApp = Nothing
243 End Try
244 End Sub
245 End Class
